package spring.test4;

import com.blackhorse.domain.Account;
import com.blackhorse.dto.AccountRowMapper;
import org.junit.Before;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

import java.util.List;

/**
 * JdbcTemplate CRUD
 */
public class Demo1_JdbcTemplate {
    private ApplicationContext applicationContext;
    private JdbcTemplate jdbcTemplate;

    @Before
    public void init() {
        String xmlPath = "jdbcTemplate/beans.xml";
        applicationContext = new ClassPathXmlApplicationContext(xmlPath);
        jdbcTemplate = (JdbcTemplate) applicationContext.getBean("jdbcTemplate");
    }

    //简单执行sql操作
    @Test
    public void basicExecute() {
        jdbcTemplate.execute("update account set money = 550 where id = 5");
    }

    //插入
    @Test
    public void testInsert() {
        Account account = new Account();
        account.setName("张大三");
        account.setMoney(1234.0f);
        jdbcTemplate.update("insert into account(name, money) values(?, ?)",
                account.getName(), account.getMoney());
    }

    //删除
    @Test
    public void testDelete() {
        jdbcTemplate.update("delete from account where id = ?", 6);
    }

    //更新
    @Test
    public void testUpdate() {
        Account account = new Account();
        account.setId(5);
        account.setName("张三三");
        account.setMoney(4321.0f);
        jdbcTemplate.update("update account set name = ?, money = ? where id = ?",
                account.getName(), account.getMoney(), account.getId());
    }

    /**
     * 查询单个，利用属性名封装结果集
     * 属性名与表的列名相同才能成功封装
     */
    @Test
    public void testQueryOne() {
        String sql = "select * from account where id = ?";
        Account account = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<Account>(Account.class), 2);
        System.out.println(account);
    }

    /**
     * 查询多个，利用属性名封装结果集
     * 属性名与表的列名相同才能成功封装
     */
    @Test
    public void testQueryList() {
        String sql = "select * from account where money > ?";
        List<Account> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<Account>(Account.class), 1000f);
        for (Account account : list) {
            System.out.println(account);
        }
    }

    /**
     * 查询多个，利用自定义映射策略封装结果集
     * 自定义结果集映射策略类：com.blackhorse.jdbcTemplate.AccountRowMapper
     * 列名和属性名不同也能成功封装（前提是在AccountRowMapper中指定好映射关系）
     */
    @Test
    public void testQueryWithCustomRowMapper() {
        String sql = "select * from account where money > ?";
        List<Account> list = jdbcTemplate.query(sql, new AccountRowMapper(), 1000f);
        for (Account account : list) {
            System.out.println(account);
        }
    }

    /**
     * 聚合函数查询
     */
    @Test
    public void testQueryForObject() {
        String sql = "select count(*) from account where money > ?";
        long count = jdbcTemplate.queryForObject(sql, Long.class, 1000f);
        System.out.println("查询记录数：" + count);
    }
}
